/** Java JDBC 2 Batch update:
   In cases like insert and delete many rows by many INSERT and DELETE SQL statements,
the performance decreases if mutiple those statements are sent to server individual.
Java 1.2 JDBC allows you lump multiple SQL update statements, and send them as whole
to the server to execute.

   The  addBatch( String sql ) and executeBatch() of Statement can be used to gether
and execute multiple SQL statements.
*/

import java.sql.*;

public class JDBC2BatchUpdate {

	Connection cnn;

	String url = "jdbc:oracle:thin:@cheval.cs.csubak.edu:1521:TEST",
		 user="scott", passwd="tiger";

	public JDBC2BatchUpdate() {
	   try {
            	try { Class.forName("oracle.jdbc.driver.OracleDriver"); }
		catch (ClassNotFoundException ee) { ee.printStackTrace(); System.exit(-1); }

		cnn = DriverManager.getConnection(url, user, passwd);
		cnn.setAutoCommit(false);
	   } catch (SQLException e ) { e.printStackTrace(); System.exit(-1); }
	}

	public static void main(String argv[]) {
		JDBC2BatchUpdate ex2 = new JDBC2BatchUpdate(); // get connected, and create stmt.

	    try {
		Statement  stmt = ex2.cnn.createStatement() ; 

		// Make a batch of SQL update statements.
		stmt.addBatch("DELETE FROM emp WHERE EMPNO = 1234 OR EMPNO = 2345");
		stmt.addBatch("INSERT INTO emp VALUES(1234, 'H. Wang', 'Prof', 7902, '1-Feb-1988', 2000, null, 10)");
		stmt.addBatch("INSERT INTO emp VALUES(2345, 'Sally', 'Salesman', 7902, to_date('1/2/2000', 'mm/dd/yyyy'), 1000, 800, 10)");

		int cnts[] = stmt.executeBatch();

		System.out.println("Records update by each of update statement in the batch:");
		for ( int i = 0; i < cnts.length; i ++ ) 
			System.out.println( "Statement " +  (i+1) + " updates " + cnts[i] + " rows");
		ex2.cnn.commit();
		stmt.close(); ex2.cnn.close();
	   } catch (SQLException e ) { e.printStackTrace(); }
	}

}